﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading.Tasks;
using DataTier;
using System.Data;

namespace DBLayer
{
    public class DBCustomer
    {
        private static SqlCommand dbCmd = null;

        public static List<Customer> GetCustomers()
        {
            List<Customer> returnList = new List<Customer>();
            string sql = "select * from Customer";
            dbCmd = AccessDBSQLClient.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

            Customer c;

            while (dbReader.Read())
            {
                c = new Customer(Convert.ToInt32(dbReader["cId"].ToString()), dbReader["cName"].ToString());
                returnList.Add(c);
            }
            AccessDBSQLClient.Close();
            return returnList;

        }

        //public static List<Subscription> GetCustomerSubscriptions(int cId)
        //{
        //    List<Subscription> returnList = new List<Subscription>();
        //    string sql = "select S.sId, ST.stName,CM.cmDescription "
        //               + "from Customer C, Subscription S, SubscriptionType ST, CarModel CM "
        //               + "where C.cId = S.cId "
        //               + "and S.stId = ST.stId "
        //               + "and S.cmId = CM.cmId "
        //               + "and C.cId = " + cId;
        //    dbCmd = AccessDBSQLClient.GetDbCommand(sql);

        //    IDataReader dbReader;
        //    dbReader = dbCmd.ExecuteReader();

        //    Subscription s;

        //    while (dbReader.Read())
        //    {
        //        //s = new Subscription();
        //        s = new Subscription((int)(dbReader["sId"]).ToString(),
        //                dbReader["stName"].ToString(),
        //                dbReader["cmDescription"].ToString());
        //        returnList.Add(s);
        //    }
        //    AccessDBSQLClient.Close();
        //    return returnList;

        //}


        //public static void CreateCustomer(int custNo,string name)
        //{
        //    string sql = @"insert into customer values (" + custNo + ",'" + name + "')";
        //    SqlCommand cmd = AccessDBSQLClient.GetDbCommand(sql);
        //    cmd.ExecuteNonQuery();
        //    AccessDBSQLClient.Close();
        //}

        //public static Customer FindCustomerByNo(int custNo)
        //{
        //    string sql = @"select * from Customer where custNo = "
        //                    +custNo;
        //    dbCmd = AccessDBSQLClient.GetDbCommand(sql);
        //    IDataReader dbReader;

        //    dbReader = dbCmd.ExecuteReader();

        //    Customer c;

        //    if (dbReader.Read())
        //        //c = new Customer(Convert.ToInt32(dbReader["custNo"].ToString()),
        //        //    dbReader["name"].ToString());
        //        c = new Customer((int)(dbReader["custNo"]),
        //            dbReader["name"].ToString());
        //    else
        //        c = null;
        //    dbReader.Close();
        //    AccessDBSQLClient.Close();
        //    return c;
        //}

        //public static Customer FindCustomerByName(string custName)
        //{
        //    string sql = @"select * from Customer where name = '"
        //                    + custName+@"'";
        //    dbCmd = AccessDBSQLClient.GetDbCommand(sql);
        //    IDataReader dbReader;

        //    dbReader = dbCmd.ExecuteReader();

        //    Customer c;

        //    if (dbReader.Read())
        //        //c = new Customer(Convert.ToInt32(dbReader["custNo"].ToString()),
        //        //    dbReader["name"].ToString());
        //        c = new Customer((int)(dbReader["custNo"]),
        //            dbReader["name"].ToString());
        //    else
        //        c = null;
        //    dbReader.Close();
        //    AccessDBSQLClient.Close();
        //    return c;
        //}
    }
//}
}
